<?php
class Chuyenbay_model extends CI_Model{
    function __construct() {
        parent::__construct();
    }
    private $tbl='Chuyenbay';
    function load_chuyenbay($date_di,$person,$from,$to){
        $DB=$this->load->database('khachhang', TRUE);
        $where=array(
           "TO_CHAR(".$this->tbl.".thoidiemdi,'YYYY-MM-DD')="=>$DB->escape($date_di),
           $this->tbl.'.soghetrong >='=>$person,
           'Tuyenbay.MSG_di'=>$from,
           'Tuyenbay.MSG_den'=>$to,
           'Trangthai'=>"'CB'"
        );
        
        $DB->from($this->tbl);
        $DB->where($where);
        $DB->join('Maybay','maybay.MSMB='.$this->tbl.'.MSMB');
        $DB->join('Loaimaybay','loaimaybay.MSLMB=maybay.MSLMB');
        $DB->join('Tuyenbay','tuyenbay.MSTB='.$this->tbl.'.MSTB');
        $query=$DB->get();
        return $query;
    }
    function load_by_id($MSCB) {
        $DB=$this->load->database('khachhang',TRUE);
        return $DB->get_where($this->tbl,array('MSCB'=>$DB->escape($MSCB)));
    }
    function insert($data) {
        $DB=$this->load->database('nhanvien',TRUE);
        $sql=$DB->insert_string($this->tbl,$data);
        $DB->query($sql);
    }
    function search($start,$end) {
        $DB=$this->load->database('nhanvien', TRUE);
        $where=array(
           $this->tbl.'.Thoidiemdi >='=>"to_timestamp(".$DB->escape($start).", 'YYYY-MM-DD')",
           $this->tbl.'.Thoidiemdi <'=>"to_timestamp('".$end." 23:59', 'YYYY-MM-DD HH24:MI')"
        );
        
        $DB->from($this->tbl);
        $DB->where($where);
        $DB->join('Maybay','maybay.MSMB='.$this->tbl.'.MSMB');
        $DB->join('Loaimaybay','loaimaybay.MSLMB=maybay.MSLMB');
        $DB->join('Tuyenbay','tuyenbay.MSTB='.$this->tbl.'.MSTB');
        $DB->order_by($this->tbl.'.thoidiemdi','ASC');
        return $DB->get();
    }
    function load_ghe_trong($MSCB){
        $DB=$this->load->database('nhanvien',TRUE);
        return $DB->query('SELECT GHESO FROM HR.GHENGOI WHERE MSLMB=(SELECT MSLMB FROM HR.MAYBAY WHERE MSMB=(SELECT MSMB FROM HR.CHUYENBAY WHERE MSCB='.$DB->escape($MSCB).')) 
                           MINUS 
                           SELECT G.GHESO FROM HR.KHACHHANG K JOIN HR.GHEKHACH G ON K.mskh=G.mskh WHERE K.MSCB='.$DB->escape($MSCB));
    }
    function load_ghetrong_loaighe($MSCB,$loaighe){
        $DB=$this->load->database('nhanvien',TRUE);
        return $DB->query('SELECT GHESO FROM HR.GHENGOI WHERE MSLMB=(SELECT MSLMB FROM HR.MAYBAY WHERE MSMB=(SELECT MSMB FROM HR.CHUYENBAY WHERE MSCB='.$DB->escape($MSCB).')) AND LOAIGHE='.$DB->escape($loaighe).'
                           MINUS 
                           SELECT G.GHESO FROM HR.KHACHHANG K JOIN HR.GHEKHACH G ON K.mskh=G.mskh WHERE K.MSCB='.$DB->escape($MSCB));
    }
    function taolich($data){
        $DB=$this->load->database('nhanvien',TRUE);
        $data['SOGHETRONG']=0;
        $sql = $DB->insert_string($this->tbl,$data);
        $DB->query($sql);
    }
    function load_by_randcode($rand_code) {
        $DB=$this->load->database('nhanvien',TRUE);
        $result=$DB->get_where($this->tbl,array('RAND_CODE'=>$DB->escape($rand_code)));
        
        $DB->where('RAND_CODE',$DB->escape($rand_code));
        $DB->update($this->tbl,array('RAND_CODE'=>"''"));
        return $result;
    }
}

